/*
 * Copyright 2011 J. Auer, J. Andert, F.-J. Jendrusch, U. Celebi
 *
 * This program is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 *
 * This program is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 *
 * You should have received a copy of the GNU General Public License
 * along with this program.  If not, see <http://www.gnu.org/licenses/>.
 */
package de.fuberlin.replication.notepad.db;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;

import com.example.android.notepad.Note;

/**
 * This DAO is responsible for modifying the notes data in the DB. For convenience while testing, there are currently
 * more methods implemented than would be absolutely necessary.
 */
public class NotesDAO extends SQLiteDAO {
	/**
	 * @param connection SQLite connection
	 */
	public NotesDAO(Connection connection) {
		super(connection);
	}

	/**
	 * Get note with the given title.
	 * 
	 * @param title Title of note to get
	 * @return Note with specific ID or all notes
	 * @throws SQLException If couldn't query DB for notes.
	 */
	public Note get(String title) throws SQLException {
		Connection conn = getConnection();

		PreparedStatement prep = conn.prepareStatement("SELECT * FROM notes WHERE title = ?");
		prep.setString(1, title);
		try{

			ResultSet res = prep.executeQuery();

			Note n	= new Note(res.getString(1), res.getString(2));
			res.close();
			
			return n;
		}catch(SQLException e){
			return null;
		}
	}

	/**
	 * Get all notes.
	 * 
	 * @return All notes
	 * @throws SQLException If couldn't query DB for notes
	 */
	public Iterable<Note> getAll() throws SQLException {
		Connection conn = getConnection();

		PreparedStatement prep = conn.prepareStatement("SELECT * FROM notes");
		ResultSet res = prep.executeQuery();

		return new NotesIterable(res);
	}

	/**
	 * @param n Note object to be inserted into the DB
	 * @throws SQLException If could'nt insert into DB
	 */
	public void set(Note n) throws SQLException {
		Connection conn = getConnection();

		PreparedStatement prep = conn
				.prepareStatement("INSERT INTO notes (title, note) VALUES (?, ?)");

		prep.setString(1, n.getTitle());
		prep.setString(2, n.getNote());

		prep.executeUpdate();
	}

	/**
	 * Save a new note to the DB.
	 * 
	 * @param title Title of the note
	 * @param note Text of the note
	 * @throws SQLException If couldn't save note
	 */
	public void set(String title, String note) throws SQLException {
		set(new Note(title, note));
	}

	/**
	 * Delete given note.
	 * 
	 * @param title Title of note
	 * @throws SQLException If couldn't delete note
	 */
	public void delete(String title) throws SQLException {
		Connection conn = getConnection();

		PreparedStatement prep = (title.equals("all")) ?
				conn.prepareStatement("DELETE FROM notes") :
				conn.prepareStatement("DELETE FROM notes WHERE title = " + title);

		prep.executeUpdate();
	}

	/**
	 * Delete given note.
	 * 
	 * @param n Note to be deleted
	 * @throws SQLException If couldn't delete note
	 */
	public void delete(Note n) throws SQLException {
		delete(n.getTitle());
	}

	/**
	 * Edit a given Note.
	 * 
	 * @param n Note to be edited
	 * @throws SQLException If couldn't edit note
	 */
	public void edit(String title, Note n) throws SQLException {
		Connection conn = getConnection();
		PreparedStatement prep = conn
				.prepareStatement("UPDATE notes SET title = ?, note = ? WHERE title = ?");
		prep.setString(1, n.getTitle());
		prep.setString(2, n.getNote());
		prep.setString(3, title);
		prep.executeUpdate();
	}
}